unit uOfLiberar;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, uPai_new, Menus, DB, StdCtrls, Buttons, ExtCtrls, MMJPanel,
  FMTBcd, DBClient, Provider, SqlExpr, Grids, DBGrids, JvExDBGrids,
  JvDBGrid, JvExControls, JvLabel, Mask, DBCtrls, JvExMask, JvToolEdit,
  JvBaseEdits, rpcompobase, rpvclreport, DBXpress;

type
  TfOfLiberar = class(TfPai_new)
    sqlComp: TSQLDataSet;
    dspComp: TDataSetProvider;
    cdsComp: TClientDataSet;
    dsComp: TDataSource;
    gridProd: TJvDBGrid;
    sqlDetOf: TSQLDataSet;
    sqlDetOfDOF_CODIGO: TFMTBCDField;
    sqlDetOfDOF_CODIOF: TStringField;
    dspDetOf: TDataSetProvider;
    cdsDetOf: TClientDataSet;
    cdsDetOfDOF_CODIGO: TFMTBCDField;
    cdsDetOfDOF_CODIOF: TStringField;
    dsDetOf: TDataSource;
    sqlDetOfSALDO: TFMTBCDField;
    cdsDetOfSALDO: TFMTBCDField;
    sqlDetOfDOF_QTDPRG: TFMTBCDField;
    cdsDetOfDOF_QTDPRG: TFMTBCDField;
    Panel1: TPanel;
    gridComp: TJvDBGrid;
    Panel2: TPanel;
    rgOf: TComboBox;
    Label1: TLabel;
    lblCodigo: TJvLabel;
    sqlDetOfPRO_CODPRO: TStringField;
    sqlDetOfPRO_DESCRI: TStringField;
    cdsDetOfPRO_CODPRO: TStringField;
    cdsDetOfPRO_DESCRI: TStringField;
    lblDescri: TJvLabel;
    Label2: TLabel;
    Label3: TLabel;
    BitBtn6: TBitBtn;
    lblProdDescri: TLabel;
    edCod: TEdit;
    edQtde: TJvCalcEdit;
    sds: TSQLDataSet;
    chkVerTodas: TCheckBox;
    sqlCompOFC_CODIGO: TFMTBCDField;
    sqlCompOFC_COFDET: TFMTBCDField;
    sqlCompOFC_CODPRO: TStringField;
    sqlCompOFC_QTDEMP: TFMTBCDField;
    sqlCompOFC_QTREAL: TFMTBCDField;
    sqlCompPRO_DESCRI: TStringField;
    cdsCompOFC_CODIGO: TFMTBCDField;
    cdsCompOFC_COFDET: TFMTBCDField;
    cdsCompOFC_CODPRO: TStringField;
    cdsCompOFC_QTDEMP: TFMTBCDField;
    cdsCompOFC_QTREAL: TFMTBCDField;
    cdsCompPRO_DESCRI: TStringField;
    sqlCompOFC_QTCOMP: TFMTBCDField;
    sqlCompSALDO: TFMTBCDField;
    cdsCompOFC_QTCOMP: TFMTBCDField;
    cdsCompSALDO: TFMTBCDField;
    sqlCompOFC_QTCOMP2: TStringField;
    sqlCompSALDO2: TStringField;
    cdsCompOFC_QTCOMP2: TStringField;
    cdsCompSALDO2: TStringField;
    sqlVerCCusto: TSQLQuery;

    procedure FormShow(Sender: TObject);
    procedure gridProdCellClick(Column: TColumn);
    procedure gridProdDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure rgOfChange(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure gridCompCellClick(Column: TColumn);
    procedure btnGravarClick(Sender: TObject);
    procedure gridCompDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure btnProcurarClick(Sender: TObject);
  private
    rep1: TVCLReport;
    procedure abreGridDet(Status: SmallInt);
    procedure abreComp();
    { Private declarations }
  public
    str, codiofDesm: String;
    { Public declarations }
  end;

var
  fOfLiberar: TfOfLiberar;

implementation

uses udm, uProdProc;

{$R *.dfm}

{ TfOfLiberar }

procedure TfOfLiberar.abreGridDet(Status: SmallInt);
begin
  str := 'select DOF.*, DECODE(COD,NULL,1,0) as SALDO , PRO_CODPRO, PRO_DESCRI ' +
     ' FROM ' +
     ' ATS_DETOF DOF, ATS_OF, F_PRODS,' +
     ' (' +
     '   SELECT DISTINCT COD' +
     '   FROM' +
     '   (' +
     '     SELECT OFC_COFDET COD, OFC_QTCOMP, COMPONENTE, SALDO, COMPROMETIDO - OFC_QTCOMP COMPROMETIDO,' +
     '            DISPONIVEL + OFC_QTCOMP DISONIVEL, OFC_QTCOMP NECESSARIO' +
     '     FROM ATS_OFCONS, ATS_COMPONENTE_DISPONIVEL' +
     '     WHERE OFC_CODPRO = COMPONENTE(+)' +
     '     AND  DISPONIVEL < 0' +
     '   )' +
     ' )' +
     ' WHERE DOF.DOF_CODIOF = COF_CODIOF' +
     ' AND PRO_CODPRO = COF_CODPRO' +
     ' AND DOF_CODIGO = COD(+)';

   if (chkVerTodas.Checked = False) then
   begin
     str := str + ' AND COF_CODIOF = ' + QuotedStr(codiofDesm);
   end;

  if (Status < 3) then
  begin
    str := str + ' AND DOF_STATUS = ' + IntToStr(status);
  end
  else begin
    str := str + ' AND DOF_STATUS < ' + IntToStr(status);
  end;
  if (cdsDetOf.Active) then
    cdsDetOf.Close;
  cdsDetOf.CommandText := str + ' ORDER BY DOF_CODIGO DESC ';
  cdsDetOf.Open;
  lblCodigo.Caption := cdsDetOfPRO_CODPRO.AsString;
  lblDescri.Caption := cdsDetOfPRO_DESCRI.AsString;
end;

procedure TfOfLiberar.abreComp;
begin
  if (cdsDetOfDOF_CODIGO.AsInteger > 0) then
  begin
    str := 'select OFC_CODIGO, OFC_COFDET, OFC_CODPRO, OFC_QTDEMP, ' +
      ' OFC_QTCOMP, ' +
      ' OFC_QTREAL, PRO_DESCRI, (NVL(SALDO, 0) - NVL(OFC_QTCOMP, 0))' +
      ' as SALDO, '+
      ' ATS_VALORES(OFC_QTCOMP, 3) OFC_QTCOMP2, ATS_VALORES(' +
      '(NVL(SALDO, 0) - NVL(OFC_QTCOMP, 0)), 3) SALDO2 ' +
      ' FROM ' +
      ' ATS_OFCONS, ATS_ESTOQUE, F_PRODS ' +
      ' WHERE PRO_CODPRO = OFC_CODPRO ' +
      ' AND OFC_CODPRO = CODPRO ';
    str := str + ' AND OFC_COFDET = ' +  IntToStr(cdsDetOfDOF_CODIGO.AsInteger);
    if (cdsComp.Active) then
      cdsComp.Close;
    cdsComp.CommandText := str;
    cdsComp.Open;    
  end;
end;

procedure TfOfLiberar.FormShow(Sender: TObject);
begin
  inherited;
  abreGridDet(0);
end;

procedure TfOfLiberar.gridProdCellClick(Column: TColumn);
begin
  inherited;
  abreComp;
  lblCodigo.Caption := cdsDetOfPRO_CODPRO.AsString;
  lblDescri.Caption := cdsDetOfPRO_DESCRI.AsString;
end;

procedure TfOfLiberar.gridProdDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  //inherited;
  If (cdsDetOfSALDO.asFloat = 1) Then
  begin
    gridProd.Canvas.Brush.Color := clWhite;
    gridProd.Canvas.Font.Color := clBlack;
    gridProd.Canvas.FillRect(Rect);
    gridProd.DefaultDrawColumnCell(Rect,DataCol,Column,State);
  end
  Else
  begin
    gridProd.Canvas.Brush.Color := clRed;
    gridProd.Canvas.Font.Color := clBlack;
    gridProd.Canvas.FillRect(Rect);
    gridProd.DefaultDrawColumnCell(Rect,DataCol,Column,State);
  end;
end;

procedure TfOfLiberar.rgOfChange(Sender: TObject);
begin
  //inherited;
  abreGridDet(rgOf.ItemIndex);
  if (cdsComp.Active) then
    cdsComp.Close;
end;

procedure TfOfLiberar.BitBtn6Click(Sender: TObject);
begin
  fProdProc.cdsProc.CommandText := 'SELECT DFC_CDCOMP as PRO_CODPRO, PRO_DESCRI, ' +
    ' NVL(DFC_QTDITE,0) * NVL(DOF_QTDPRG, 0) as PRO_VOLEMB FROM' +
    ' ATS_DETOFCONS, ATS_OFCONS, ATS_DETOF, F_PRODS WHERE DFC_COFCON = OFC_CODIGO ' +
    ' AND DOF_CODIGO = OFC_COFDET AND PRO_CODPRO = DFC_CDCOMP AND PRO_DESCRI LIKE :PROD ' +
    ' AND OFC_COFDET = ' + IntToStr(cdsComp.Fields[1].asInteger) + ' AND OFC_CODPRO = ' +
    QuotedStr(cdsComp.Fields[2].AsString) + ' AND DFC_CDCOMP <> ' + QuotedStr(cdsComp.Fields[2].AsString) ;
  fProdProc.ShowModal;
  edCod.Text            := fProdProc.codPro;
  lblProdDescri.Caption := fProdProc.ProDescri;
  edQtde.Value          := fProdProc.cdsProc.Fields[2].AsFloat;
  fProdProc.cdsProc.Close;
end;


procedure TfOfLiberar.gridCompCellClick(Column: TColumn);
begin
  edCod.Text            := cdsCompOFC_CODPRO.AsString;
  lblProdDescri.Caption := cdsCompPRO_DESCRI.AsString;
  edQtde.Value          := cdsCompSALDO.AsFloat;
end;

procedure TfOfLiberar.btnGravarClick(Sender: TObject);
begin
  // Validar o item digitado
  if (fProdProc.cdsProc.Active) then
    fProdProc.cdsProc.Close;
  fProdProc.cdsProc.CommandText := 'SELECT DFC_CDCOMP as PRO_CODPRO, PRO_DESCRI, ' +
    ' NVL(DFC_QTDITE,0) * NVL(DOF_QTDPRG, 0) as PRO_VOLEMB FROM' +
    ' ATS_DETOFCONS, ATS_OFCONS, ATS_DETOF, F_PRODS WHERE DFC_COFCON = OFC_CODIGO ' +
    ' AND DOF_CODIGO = OFC_COFDET AND PRO_CODPRO = DFC_CDCOMP AND PRO_CODPRO = ' +
    QuotedStr(edCod.Text) +
    ' AND OFC_COFDET = ' + IntToStr(cdsComp.Fields[1].asInteger) + ' AND OFC_CODPRO = ' +
    QuotedStr(cdsComp.Fields[2].AsString) + ' AND DFC_CDCOMP <> ' + QuotedStr(cdsComp.Fields[2].AsString) ;;

  fProdProc.cdsProc.Open;

  if (fProdProc.cdsProc.IsEmpty) then
  begin
    MessageDlg('Produto nao cadastro na estrutura.', mtError, [mbOK],0);
    exit;
  end;
  // Verifica se tem Estoque
  {str := 'SELECT SALDO FROM ATS_ESTOQUE WHERE CODPRO = ' + QuotedStr(edCod.Text);
  IF (sds.Active) then
    sds.Close;
  sds.CommandText := str;
  sds.Open;
  if (sds.Fields[0].AsFloat > fProdProc.cdsProc.Fields[2].AsFloat) then
  }
  begin
    DecimalSeparator := '.';
    str := 'UPDATE ATS_OFCONS SET OFC_CODPRO = ' + QuotedStr(edCod.Text);
    str := str + ' ,OFC_QTCOMP = ' + FloatToStr(fProdProc.cdsProc.Fields[2].AsFloat);
    str := str + ' WHERE OFC_CODIGO = ' + IntToStr(cdsComp.Fields[0].asInteger);
    Try
      dm.SQLConnection1.ExecuteDirect(str);
      DecimalSeparator := ',';
      MessageDlg('Item alterado com sucesso.', mtInformation, [mbOK],0);
      abreComp;
    Except
      DecimalSeparator := ',';
      MessageDlg('Item ja Incluido.', mtError, [mbOK],0);
    end;
  end
  {else begin
    MessageDlg('Estoque Insuficiente.', mtWarning, [mbOK],0);
  end;}
end;

procedure TfOfLiberar.gridCompDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  inherited;
  If (cdsCompSALDO.asFloat > 0) Then
  begin
    gridComp.Canvas.Brush.Color := clWhite;
    gridComp.Canvas.Font.Color := clBlack;
    gridComp.Canvas.FillRect(Rect);
    gridComp.DefaultDrawColumnCell(Rect,DataCol,Column,State);
  end
  Else
  begin
    gridComp.Canvas.Brush.Color := clRed;
    gridComp.Canvas.Font.Color := clBlack;
    gridComp.Canvas.FillRect(Rect);
    gridComp.DefaultDrawColumnCell(Rect,DataCol,Column,State);
  end;

end;

procedure TfOfLiberar.btnProcurarClick(Sender: TObject);
var modelo, st: String;
 Save_Cursor:TCursor;
 TD: TTransactionDesc;
begin
  modelo := '1';
  if (sqlVerCCusto.Active) then
    sqlVerCCusto.Close;
  sqlVerCCusto.SQL.Clear;
  st := 'SELECT DISTINCT PCC_NOMCON CCUSTO ' +
        ' FROM F_ROTVER, F_MAQUINA, F_CELULA, C_PLANCC, ATS_OF '+
        ' WHERE COF_CODIOF = ' + QuotedStr(cdsDetOfDOF_CODIOF.AsString) +
        ' AND VER_CODCNP = COF_CODINP AND VER_CODMAQ = MAQ_CODIGO ' +
        ' AND MAQ_CELULA = CEL_CODCEL AND CEL_CCUSTO = PCC_NUMCON ' +
        ' AND VER_ROTEIR = COF_ROTEIR ' +
        ' AND ((VER_TERCEI = 1) OR (VER_PROHOR > 0)) ';

  sqlVerCCusto.SQL.Add(st);
  sqlVerCCusto.Open;
  if (sqlVerCCusto.Fields[0].AsString = 'INJECAO') then
    modelo := '2';

  if (modelo = '1') then
  begin
  try
    rep1 := TVCLReport.Create(self);
    rep1.PrinterSetup;
    //inherited;
    rep1.FileName := 'Of_rel.rep';
    rep1.Report.DatabaseInfo.Items[0].SQLConnection := dm.SQLConnection1;

    rep1.Report.DataInfo.Items[0].SQL := 'select  COF_CODIOF, COF_CODPRO, DOF_CODLOT,  ' +
      ' COF_DTPREV, TO_CHAR(COF_QTPROG, ' + QuotedStr('FM9999999999.0') + ') COF_QTPROG, COF_STATUS, COF_DATINI, DOF_QTDPRG, DOF_STATUS, '+
      ' DOF_CODIGO, DOF_QTDEMB , PRO_DESCRI, COF_DTENCE, DOF_LOGUSU, COF_CODINP, '+
      QuotedStr('O') + ' || LPAD(TO_CHAR(DOF_CODIGO),8,' + QuotedStr('0') + ') || '+ QuotedStr('F') + ' AS CODOF ' +
      ' from ATS_OF, ATS_DETOF, F_PRODS' +
      ' WHERE   ' +
      ' DOF_CODIOF = COF_CODIOF '+
      ' AND COF_CODPRO = PRO_CODPRO '+
      ' AND DOF_CODIGO = ' + InttoStr(cdsDetOfDOF_CODIGO.AsInteger);

    //rep.Report.Params.ParamByName('POF').Value := cdsDetOfDOF_CODIOF.AsString;
    //rep.Report.Params.ParamByName('PCODDET').Value :=

      rep1.Report.DataInfo.Items[2].SQL := 'SELECT DISTINCT VER_CODSEQ OPERACAO, substr(VER_HISTOR, 1, 250) TAREFA,' +
        ' CEL_DESCRI CELULA, PCC_NUMCON NUMCCUSTO, PCC_NOMCON CCUSTO, MAQ_DESCRI MAQUINA ' +
        ' FROM F_ROTVER, F_MAQUINA, F_CELULA, C_PLANCC, ATS_OF '+
        ' WHERE COF_CODIOF = ' + QuotedStr(cdsDetOfDOF_CODIOF.AsString) +
        ' AND VER_CODCNP = COF_CODINP AND VER_CODMAQ = MAQ_CODIGO ' +
        ' AND MAQ_CELULA = CEL_CODCEL AND CEL_CCUSTO = PCC_NUMCON ' +
        ' AND VER_ROTEIR = COF_ROTEIR ' +
        ' AND ((VER_TERCEI = 1) OR (VER_PROHOR > 0)) ' +
        ' order by to_number(VER_CODSEQ) ';

      rep1.Report.DataInfo.Items[3].SQL := 'SELECT TO_CHAR(OFC_QTCOMP,' +
        QuotedStr('FM999999999.0') + ') OFC_QTCOMP, OFC_CODPRO, ' +
        ' PRO_DESCRI , PRO_UNIMED, TPP_DESCRI , ROWNUM ITEM' +
        ' FROM ATS_OFCONS, F_PRODS , F_TPPROD ' +
        ' WHERE OFC_COFDET = ' + InttoStr(cdsDetOfDOF_CODIGO.AsInteger) +
        ' AND PRO_CODPRO = OFC_CODPRO  AND PRO_TPPROD = TPP_CODIGO';

    Rep1.Title := Rep1.FileName;
    Rep1.Execute;
    Rep1.Destroy;
  except
    MessageDlg('Erro na impressao, O.F. nao liberada !', mtError, [mbOK],0);
    exit;
  end;
  end; // fim modelo 1


  if (modelo = '2') then
  begin
  try
    rep1 := TVCLReport.Create(self);
    rep1.PrinterSetup;
    //inherited;
    rep1.FileName := 'Of_rel2.rep';
    rep1.Report.DatabaseInfo.Items[0].SQLConnection := dm.SQLConnection1;

    rep1.Report.DataInfo.Items[0].SQL := 'select  COF_CODIOF, COF_CODPRO, DOF_CODLOT,  ' +
      ' COF_DTPREV, TO_CHAR(COF_QTPROG, ' + QuotedStr('FM9999999999.0') + ') COF_QTPROG, COF_STATUS, COF_DATINI, DOF_QTDPRG, DOF_STATUS, '+
      ' DOF_CODIGO, DOF_QTDEMB , PRO_DESCRI, COF_DTENCE, DOF_LOGUSU, COF_CODINP, '+
      QuotedStr('O') + ' || LPAD(TO_CHAR(DOF_CODIGO),8,' + QuotedStr('0') + ') || '+ QuotedStr('F') + ' AS CODOF ' +
      ' from ATS_OF, ATS_DETOF, F_PRODS' +
      ' WHERE   ' +
      ' DOF_CODIOF = COF_CODIOF '+
      ' AND COF_CODPRO = PRO_CODPRO '+
      ' AND DOF_CODIGO = ' + InttoStr(cdsDetOfDOF_CODIGO.AsInteger);

    //rep.Report.Params.ParamByName('POF').Value := cdsDetOfDOF_CODIOF.AsString;
    //rep.Report.Params.ParamByName('PCODDET').Value :=

      rep1.Report.DataInfo.Items[2].SQL := 'SELECT DISTINCT VER_CODSEQ OPERACAO, substr(VER_HISTOR, 1, 250) TAREFA,' +
        ' CEL_DESCRI CELULA, PCC_NUMCON NUMCCUSTO, PCC_NOMCON CCUSTO, MAQ_DESCRI MAQUINA ' +
        ' FROM F_ROTVER, F_MAQUINA, F_CELULA, C_PLANCC, ATS_OF '+
        ' WHERE COF_CODIOF = ' + QuotedStr(cdsDetOfDOF_CODIOF.AsString) +
        ' AND VER_CODCNP = COF_CODINP AND VER_CODMAQ = MAQ_CODIGO ' +
        ' AND MAQ_CELULA = CEL_CODCEL AND CEL_CCUSTO = PCC_NUMCON ' +
        ' AND VER_ROTEIR = COF_ROTEIR ' +
        ' AND ((VER_TERCEI = 1) OR (VER_PROHOR > 0)) ' +
        ' order by to_number(VER_CODSEQ) ';

      rep1.Report.DataInfo.Items[3].SQL := 'SELECT TO_CHAR(OFC_QTCOMP,' +
        QuotedStr('FM999999999.0') + ') OFC_QTCOMP, OFC_CODPRO, ' +
        ' PRO_DESCRI , PRO_UNIMED, TPP_DESCRI , ROWNUM ITEM' +
        ' FROM ATS_OFCONS, F_PRODS , F_TPPROD ' +
        ' WHERE OFC_COFDET = ' + InttoStr(cdsDetOfDOF_CODIGO.AsInteger) +
        ' AND PRO_CODPRO = OFC_CODPRO  AND PRO_TPPROD = TPP_CODIGO';

    Rep1.Title := Rep1.FileName;
    Rep1.Execute;
    Rep1.Destroy;
  except
    MessageDlg('Erro na impressao, O.F. nao liberada !', mtError, [mbOK],0);
    exit;
  end;
  end; // fim modelo 2

  str := 'UPDATE ATS_DETOF SET DOF_STATUS = 1';
  str := str + ' WHERE DOF_CODIGO = ' + IntToStr(cdsDetOfDOF_CODIGO.AsInteger);
  TD.TransactionID := 1;
  TD.GlobalID := 0;
  TD.IsolationLevel := xilREADCOMMITTED;
  Try
    dm.SQLConnection1.StartTransaction(TD);
    dm.SQLConnection1.ExecuteDirect(str);
    dm.SQLConnection1.Commit(TD);
    DecimalSeparator := ',';
    MessageDlg('O.F. impressa e liberada com sucesso.', mtInformation, [mbOK],0);
    abreComp;
  Except
    dm.SQLConnection1.Rollback(TD);  
    MessageDlg('Erro liberacao da O.F. !', mtError, [mbOK],0);
  end;

end;

end.
